<html>
<title>UI API Documentation</title>
<h1>
<!--
	Summary: The PHP script below reads stored procedures directly
	from the MySQL database schema and parses out special comments
	within the code to provide a useful documentation tool for
	the User Interface team to use.
	Author: Doug Hamilton
-->
Database API Documentation for UI Team
</h1>
<p>The UI's database user only allows the use of stored procedures and 
functions.  The routines listed below are pulled directly from the 
database.  They are not necessarily correctly implemented but are
documented to explain their functionality.
</p>
<p>
Here is an example of the comments which this script parses:<br>
<pre>
DELIMITER $$

DROP FUNCTION IF EXISTS `Users`.`isValidUserLogin`$$
CREATE DEFINER=`dhamilton`@`` FUNCTION `isValidUserLogin`(name VARCHAR(32),pass VARCHAR(32)) RETURNS tinyint(1)
BEGIN
-- DESC: Validates a username and password combination
-- IN: Username The username of the user.  Must be less than 33 characters.
-- IN: Password The password of the user.  Must be less than 33 characters.
-- RETURN: Returns TRUE if the username/password combination is valid.  FALSE if either the username doesn't exist or the password is incorrect.

...
</pre>
</p>
<h2>
Currently Available Routines
</h2>

<?php

$link = mysql_connect("localhost","server","roomnanny101");
if(!link) {
die( "Failed to connect to server");
}

$res = mysql_query("SELECT routine_name, routine_definition, routine_schema FROM information_schema.routines ORDER BY routine_schema, routine_name");

if(!$res)
{
	echo "Query error:".mysql_error()."\n";
	die();
}

echo "<ul>";

while($row = mysql_fetch_row($res)){
	// Ignore any database containing the word "Debug"
	if(strpos($row[2],"Debug")) continue;
	// Ignore any without description tag
	if(strpos($row[1],"DESC:") === FALSE) { continue; }
	// Write name
	$output = array();
	$hasDescription = FALSE;
	$hideThis = FALSE;
	$curStr = $row[1];
	$lines = preg_split("/((\r(?!\n))|((?<!\r)\n)|(\r\n))/",$curStr);
	$paramNames = array();
	foreach($lines as $line)
	{
		// Ignore non-comment lines
		if(substr($line,0,2) != "--")
			continue;
		// Figure out what type of param it is
		$str = trim(substr($line,2));
		$tokens = preg_split("/[\s:]/",$str,0,PREG_SPLIT_NO_EMPTY | PREG_SPLIT_OFFSET_CAPTURE);
		if($tokens[0][0] == "IN")
		{
			$paramNames[] = $tokens[1][0];
			$output[] = "<b>IN</b><em> ".$tokens[1][0].":</em> ".substr($str,$tokens[2][1])."<br>";
		}
		else if($tokens[0][0] == "HIDDEN")
		{
			$hideThis = TRUE;
		}
		else if($tokens[0][0] == "DESC")
		{
			$hasDescription = TRUE;
			$output[] = "<b>Description:</b> ".$tokens[1][0]." ".substr($str,$tokens[2][1])."<br>";
		}
		else if($tokens[0][0] == "OUT")
		{
			$paramNames[] = $tokens[1][0];
			$output[] = "<b>OUT</b><em> ".$tokens[1][0].":</em> ".substr($str, $tokens[2][1])."<br>";
		}
		else if($tokens[0][0] == "INOUT")
		{
			$paramNames[] = $tokens[1][0];
			$output[] = "<b>INOUT</b><em> ".$tokens[1][0].":</em> ".substr($str, $tokens[2][1])."<br>";
		}
		else if($tokens[0][0] == "RETURN" || $tokens[0][0] == "RETURNS")
		{
			$output[] =  "<b>Returns: </b>".substr($str,$tokens[1][1])."<br>";
		}
	}
	if(!$hasDescription){continue;}
	if($hideThis){continue;}
	// Begin writing entry
	$name =  "<li>".$row[2].".<b>".$row[0]."</b>(";
	// Add param names
	foreach($paramNames as $p)
	{
		$name = $name."<em>".$p."</em>, ";
	}
	if(count($paramNames) > 0)
		echo substr($name, 0, -2).")<br><br>";
	else
		echo $name." )<br><br>";
	foreach($output as $o)
		echo $o;
	echo "<br><br>";

}
echo "</ul>";

mysql_close($link);
?>

</html>
